/*==============================================================*/
/* DBMS name:      ORACLE Version 11g                           */
/* Created on:     11/15/2020 10:43:29 PM                       */
/*==============================================================*/


create sequence seq_tb_category;

create sequence seq_tb_comment;

create sequence seq_tb_config;

create sequence seq_tb_file;

create sequence seq_tb_link;

create sequence seq_tb_pd;

create sequence seq_tb_posts;

create sequence seq_tb_tag;

/*==============================================================*/
/* Table: tb_category                                           */
/*==============================================================*/
create table tb_category 
(
   cate_id              INTEGER              not null,
   cate_name            VARCHAR2(100)        not null,
   cate_parent          INTEGER,
   cate_create_time     DATE                 not null,
   cate_status          NUMBER(1)            default 0 not null,
   cate_weight          NUMBER(2)            default 0 not null
);

comment on table tb_category is
'分类表';

comment on column tb_category.cate_id is
'主键';

comment on column tb_category.cate_name is
'分类名';

comment on column tb_category.cate_parent is
'父分类';

comment on column tb_category.cate_create_time is
'创建时间';

comment on column tb_category.cate_status is
'状态(是否展示)
0 展示
1 不展示';

comment on column tb_category.cate_weight is
'权重';

alter table tb_category
   add constraint PK_TB_CATEGORY primary key (cate_id);

/*==============================================================*/
/* Table: tb_comment                                            */
/*==============================================================*/
create table tb_comment 
(
   comm_id              INTEGER              not null,
   post_id              INTEGER              not null,
   tb_comm_id           INTEGER,
   comm_content         CLOB                 not null,
   comm_create_time     DATE                 not null,
   comm_is_show         NUMBER(1)            default 1 not null,
   comm_name            VARCHAR2(100)        not null,
   comm_email           VARCHAR2(50)         not null
);

comment on table tb_comment is
'评论表';

comment on column tb_comment.comm_id is
'主键';

comment on column tb_comment.post_id is
'文章外键';

comment on column tb_comment.tb_comm_id is
'评论外键';

comment on column tb_comment.comm_content is
'评论内容';

comment on column tb_comment.comm_create_time is
'评论时间';

comment on column tb_comment.comm_is_show is
'是否允许展示
0 允许
1 不允许';

comment on column tb_comment.comm_name is
'评论人';

comment on column tb_comment.comm_email is
'评论人邮箱';

alter table tb_comment
   add constraint PK_TB_COMMENT primary key (comm_id);

/*==============================================================*/
/* Index: comm_post_r_FK                                        */
/*==============================================================*/
create index comm_post_r_FK on tb_comment (
   post_id ASC
);

/*==============================================================*/
/* Index: son_fat_r_FK                                          */
/*==============================================================*/
create index son_fat_r_FK on tb_comment (
   tb_comm_id ASC
);

/*==============================================================*/
/* Table: tb_config                                             */
/*==============================================================*/
create table tb_config 
(
   conf_id              INTEGER              not null,
   conf_name            VARCHAR2(100)        not null,
   conf_value           VARCHAR2(1024)       not null,
   conf_create_time     DATE                 not null,
   conf_status          NUMBER(1)            default 0 not null
);

comment on table tb_config is
'系统配置表';

comment on column tb_config.conf_id is
'主键';

comment on column tb_config.conf_name is
'配置名';

comment on column tb_config.conf_value is
'配置值';

comment on column tb_config.conf_create_time is
'创建时间';

comment on column tb_config.conf_status is
'状态（是否禁用）
0 不禁用
1 禁用';

alter table tb_config
   add constraint PK_TB_CONFIG primary key (conf_id);

/*==============================================================*/
/* Table: tb_file                                               */
/*==============================================================*/
create table tb_file 
(
   file_id              INTEGER              not null,
   file_name            VARCHAR2(100)        not null,
   file_size            INTEGER              not null,
   file_md5             CHAR(32)             not null,
   file_type            VARCHAR2(20)         not null,
   file_create_time     DATE                 not null,
   file_update_time     DATE                 not null,
   file_is_share        NUMBER(1)            default 0 not null,
   file_describe        VARCHAR2(1024),
   file_path            VARCHAR2(100)        not null
);

comment on table tb_file is
'上传文件表';

comment on column tb_file.file_id is
'主键';

comment on column tb_file.file_name is
'文件名';

comment on column tb_file.file_size is
'文件大小';

comment on column tb_file.file_md5 is
'文件md5值';

comment on column tb_file.file_type is
'文件类型 MIME TYPE';

comment on column tb_file.file_create_time is
'创建时间';

comment on column tb_file.file_update_time is
'更新时间';

comment on column tb_file.file_is_share is
'是否分享 0 分享 1不分享';

comment on column tb_file.file_describe is
'文件描述';

comment on column tb_file.file_path is
'文件路径';

alter table tb_file
   add constraint PK_TB_FILE primary key (file_id);

/*==============================================================*/
/* Table: tb_link                                               */
/*==============================================================*/
create table tb_link 
(
   link_id              INTEGER              not null,
   link_name            VARCHAR2(100)        not null,
   link_url             VARCHAR2(1024)       not null,
   link_weight          NUMBER(2)            default 0 not null,
   link_status          NUMBER(1)            default 0 not null,
   link_create_time     DATE                 not null
);

comment on table tb_link is
'友情链接表';

comment on column tb_link.link_id is
'主键';

comment on column tb_link.link_name is
'展示名称';

comment on column tb_link.link_url is
'链接地址';

comment on column tb_link.link_weight is
'权重';

comment on column tb_link.link_status is
'状态（是否展示）
0 展示
1 不展示';

comment on column tb_link.link_create_time is
'创建时间';

alter table tb_link
   add constraint PK_TB_LINK primary key (link_id);

/*==============================================================*/
/* Table: tb_post_detail                                        */
/*==============================================================*/
create table tb_post_detail 
(
   pd_conent_raw        CLOB                 not null,
   pd_content           CLOB                 not null,
   pd_is_reward         NUMBER(1)            not null,
   pd_is_comment        NUMBER(1)            not null,
   pd_pwd               CHAR(32),
   pd_id                INTEGER              not null
);

comment on table tb_post_detail is
'文章详情表';

comment on column tb_post_detail.pd_conent_raw is
'文章原始数据';

comment on column tb_post_detail.pd_content is
'文章渲染后的数据';

comment on column tb_post_detail.pd_is_reward is
'是否允许赞赏';

comment on column tb_post_detail.pd_is_comment is
'是否允许评论';

comment on column tb_post_detail.pd_pwd is
'加密的密码';

comment on column tb_post_detail.pd_id is
'主键';

alter table tb_post_detail
   add constraint PK_TB_POST_DETAIL primary key (pd_id);

/*==============================================================*/
/* Table: tb_posts                                              */
/*==============================================================*/
create table tb_posts 
(
   post_id              INTEGER              not null,
   cate_id              INTEGER,
   pd_id                INTEGER              not null,
   post_name            VARCHAR2(100)        not null,
   post_describe        VARCHAR2(255)        not null,
   post_create_time     DATE                 not null,
   post_update_time     DATE                 not null,
   post_look_num        INTEGER              default 0 not null,
   post_comment_num     INTEGER              default 0 not null,
   post_cover           VARCHAR2(255),
   post_is_top          NUMBER(1)            default 1 not null,
   post_is_encrypted    NUMBER(1)            default 1 not null,
   post_status          NUMBER(1)            default 1 not null
);

comment on table tb_posts is
'文章表';

comment on column tb_posts.post_id is
'主键';

comment on column tb_posts.cate_id is
'分类外键';

comment on column tb_posts.pd_id is
'文章详情外键';

comment on column tb_posts.post_name is
'文章名';

comment on column tb_posts.post_describe is
'文章描述';

comment on column tb_posts.post_create_time is
'创建时间';

comment on column tb_posts.post_update_time is
'更新时间';

comment on column tb_posts.post_look_num is
'浏览数';

comment on column tb_posts.post_comment_num is
'评论数';

comment on column tb_posts.post_cover is
'封面图片';

comment on column tb_posts.post_is_top is
'是否置顶
0 置顶
1 不置顶
';

comment on column tb_posts.post_is_encrypted is
'是否加密
0 加密
1 不加密';

comment on column tb_posts.post_status is
'文章状态
0 发布
1 草稿';

alter table tb_posts
   add constraint PK_TB_POSTS primary key (post_id);

/*==============================================================*/
/* Index: post_detail_r_FK                                      */
/*==============================================================*/
create index post_detail_r_FK on tb_posts (
   pd_id ASC
);

/*==============================================================*/
/* Index: cate_post_r_FK                                        */
/*==============================================================*/
create index cate_post_r_FK on tb_posts (
   cate_id ASC
);

/*==============================================================*/
/* Table: tb_tag                                                */
/*==============================================================*/
create table tb_tag 
(
   tag_id               INTEGER              not null,
   tag_name             VARCHAR2(100)        not null
);

comment on table tb_tag is
'标签表';

comment on column tb_tag.tag_id is
'主键';

comment on column tb_tag.tag_name is
'标签名';

alter table tb_tag
   add constraint PK_TB_TAG primary key (tag_id);

/*==============================================================*/
/* Table: tb_tag_post                                           */
/*==============================================================*/
create table tb_tag_post 
(
   tag_id               INTEGER              not null,
   post_id              INTEGER              not null
);

comment on table tb_tag_post is
'标签和文章的关系表';

comment on column tb_tag_post.tag_id is
'标签主键';

comment on column tb_tag_post.post_id is
'文章主键';

alter table tb_tag_post
   add constraint PK_TB_TAG_POST primary key (tag_id, post_id);

/*==============================================================*/
/* Index: tag_post_r_FK                                         */
/*==============================================================*/
create index tag_post_r_FK on tb_tag_post (
   tag_id ASC
);

/*==============================================================*/
/* Index: tag_post_r2_FK                                        */
/*==============================================================*/
create index tag_post_r2_FK on tb_tag_post (
   post_id ASC
);

alter table tb_category
   add constraint FK_TB_CATEG_FS_CATE_R_TB_CATEG foreign key (cate_parent)
      references tb_category (cate_id);

alter table tb_comment
   add constraint FK_TB_COMME_COMM_POST_TB_POSTS foreign key (post_id)
      references tb_posts (post_id);

alter table tb_comment
   add constraint FK_TB_COMME_SON_FAT_R_TB_COMME foreign key (tb_comm_id)
      references tb_comment (comm_id);

alter table tb_posts
   add constraint FK_TB_POSTS_CATE_POST_TB_CATEG foreign key (cate_id)
      references tb_category (cate_id);

alter table tb_posts
   add constraint FK_TB_POSTS_POST_DETA_TB_POST_ foreign key (pd_id)
      references tb_post_detail (pd_id);

alter table tb_tag_post
   add constraint FK_TB_TAG_P_TAG_POST__TB_TAG foreign key (tag_id)
      references tb_tag (tag_id);

alter table tb_tag_post
   add constraint FK_TB_TAG_P_TAG_POST__TB_POSTS foreign key (post_id)
      references tb_posts (post_id);


create or replace trigger tri_tb_cate 
   before insert or update of cate_id 
   on tb_category for each row
declare
begin
   if inserting then
      select seq_tb_category.nextVal into :new.cate_id from dual;
   else 
      select :old.cate_id into :new.cate_id from dual;
   end if;
end;

create or replace trigger tri_tb_post
    before insert or update of post_id
    on tb_posts for each row
begin
    if inserting then
        select seq_tb_posts.nextVal into :new.post_id from dual;
    else
        select :old.post_id into :new.post_id from dual;
    end if;
end;

create or replace trigger tri_tb_tag
    before insert or update of tag_id
    on tb_tag for each row
begin
    if inserting then
        :new.tag_id := seq_tb_tag.nextval;
    else
        select :old.tag_id into :new.tag_id from dual;
    end if;
end;

create or replace trigger tri_tb_pd
    before insert or update of pd_id
    on tb_post_detail for each row
begin
    if inserting then
        select seq_tb_pd.nextval into :new.pd_id from dual;
    else
        :new.pd_id := :old.pd_id;
    end if;
end;

create or replace trigger tri_tb_comm
    before insert or update of comm_id
    on tb_comment for each row
begin
    if inserting then
        :new.comm_id := seq_tb_comment.nextval;
    else
        :new.comm_id := :old.comm_id;
    end if;
end;

create or replace trigger tri_tb_conf
    before insert or update of conf_id
    on tb_config for each row
begin
    if inserting then
        :new.conf_id := seq_tb_config.nextval;
    else
        :new.conf_id := :old.conf_id;
    end if;
end;

create or replace trigger tri_tb_link
    before insert or update of link_id
    on tb_link for each row
begin
    if inserting then
        :new.link_id := seq_tb_link.nextval;
    else
        :new.link_id := :old.link_id;
    end if;
end;

create or replace trigger tri_tb_file
    before insert or update of file_id
    on tb_file for each row
begin
    if inserting then
        :new.file_id := seq_tb_file.nextval;
    else
        :new.file_id := :old.file_id;
    end if;
end;